package vn.edu.saigontech.ncms.service.impl;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;

import vn.edu.saigontech.ncms.model.info.Offering;
import vn.edu.saigontech.ncms.model.info.OfferingMember;
import vn.edu.saigontech.ncms.model.info.Receipt;
import vn.edu.saigontech.ncms.model.program.Program;
import vn.edu.saigontech.ncms.model.result.CambridgeTest;
import vn.edu.saigontech.ncms.model.result.FinalTest;
import vn.edu.saigontech.ncms.model.result.MidtermTest;
import vn.edu.saigontech.ncms.service.CambridgeTestService;
import vn.edu.saigontech.ncms.service.FinalTestService;
import vn.edu.saigontech.ncms.service.MidtermTestService;
import vn.edu.saigontech.ncms.service.OfferingMemberService;
import vn.edu.saigontech.ncms.service.OfferingService;
import vn.edu.saigontech.ncms.service.ParameterService;
import vn.edu.saigontech.ncms.service.ReceiptService;
import vn.edu.saigontech.ncms.util.Checker;
import vn.edu.saigontech.ncms.util.DateUtil;
import vn.edu.saigontech.ncms.util.UnicodeUtil;
import vn.edu.saigontech.ncms.vo.CertificateReportItem;
import vn.edu.saigontech.ncms.vo.FinalTestResultReportItem;

@SuppressWarnings("unchecked")
public class OfferingMemberServiceImpl extends HibernateGenericService<OfferingMember, Long> implements
		OfferingMemberService {

	public OfferingMember add(Long offeringReceiptId) {
		OfferingMember offeringMember = new OfferingMember();
		offeringMember.setReceipt(ReceiptService.INSTANCE.findById(offeringReceiptId));
		offeringMember.setActive(true);
		if (!Checker.isNull(super.persist(offeringMember))) {
			FinalTest finalTest = new FinalTest();
			finalTest.setOfferingMember(offeringMember);
			FinalTestService.INSTANCE.persist(finalTest);
			offeringMember.setFinalTest(finalTest);
			
			CambridgeTest cambridgeTest = new CambridgeTest();
			cambridgeTest.setOfferingMember(offeringMember);
			CambridgeTestService.INSTANCE.persist(cambridgeTest);
			offeringMember.setCambridgeTest(cambridgeTest);

			MidtermTest midtermTest = new MidtermTest();
			midtermTest.setOfferingMember(offeringMember);
			MidtermTestService.INSTANCE.persist(midtermTest);
			offeringMember.setMidtermTest(midtermTest);
		}
		return offeringMember;
	}

	@Override
	public OfferingMember edit(Long id, boolean active, String comment) {
		OfferingMember offeringMember = this.findById(id);
		offeringMember.setActive(active);
		offeringMember.setComment(comment);
		offeringMember.getReceipt().setActive(active);
		return super.persist(offeringMember);
	}

	public Collection<OfferingMember> findByOfferingId(Long offeringId) {
		Criteria criteria = this.createCriteria();
		Criteria receiptCriteria = criteria.createCriteria("receipt");
		receiptCriteria.createCriteria("offering").add(
				Restrictions.eq("id", offeringId));
		receiptCriteria.createCriteria("student").addOrder(
				Order.asc("firstName")).addOrder(Order.asc("lastName"));
		return criteria.list();
	}

	public Collection<OfferingMember> findByOfferingId(Long offeringId,
			boolean active) {
		Criteria criteria = this.createCriteria();
		criteria.add(Restrictions.eq("active", active));
		Criteria receiptCriteria = criteria.createCriteria("receipt");
		receiptCriteria.createCriteria("offering").add(
				Restrictions.eq("id", offeringId));
		receiptCriteria.createCriteria("student").addOrder(
				Order.asc("firstName")).addOrder(Order.asc("lastName"));
		return criteria.list();
	}

	@Override
	public void changeOffering(Long sourceOfferingId, Long targetOfferingId,
			boolean sourceOfferingDeleted) {
		Offering targetOffering = OfferingService.INSTANCE
				.findById(targetOfferingId);

		Collection<Receipt> receipts = ReceiptService.INSTANCE
				.findByOfferingId(sourceOfferingId);

		for (Receipt receipt : receipts) {
			receipt.setOffering(targetOffering);
			ReceiptService.INSTANCE.persist(receipt);
		}

		this.getSession().flush();
	}
	
	public Collection<CertificateReportItem> issueCertificate(Long offeringId) {
		Collection<CertificateReportItem> result = new ArrayList<CertificateReportItem>();
		Collection<OfferingMember> members = this.findByOfferingId(offeringId,
				true);
		String principalName = ParameterService.INSTANCE.findText("PCL");
		String principal = ParameterService.INSTANCE.findTitle("PCL");
		for (OfferingMember member : members) {
			if (member.passed()) {
				String name = UnicodeUtil.removeVietnamese(member.getReceipt().getStudent().getFullName());
				String level = member.getReceipt().getOffering().getLevel().getCourseName();
				Date issueDate = member.getReceipt().getOffering().getIssueDate();
				result.add(new CertificateReportItem(name, level, issueDate, principalName, principal));
			}
		}
		return result;
	}

	@Deprecated
	public Collection<CertificateReportItem> issueOldCertificate(Long offeringId) {
		Collection<CertificateReportItem> result = new ArrayList<CertificateReportItem>();
		Collection<OfferingMember> members = this.findByOfferingId(offeringId,
				true);
		String principalName = ParameterService.INSTANCE.findText("PCL");
		String principal = ParameterService.INSTANCE.findTitle("PCL");
		for (OfferingMember member : members) {
			if (member.getFinalTest().oldPassed()) {
				result.add(new CertificateReportItem(UnicodeUtil
						.removeVietnamese(member.getReceipt().getStudent()
								.getFullName()), member.getReceipt()
						.getOffering().getLevel().getCourseName(), member
						.getReceipt().getOffering().getIssueDate(), principalName, principal));
			}
		}
		return result;
	}

	@Override
	public Collection<OfferingMember> findPassedByOfferingId(Long offeringId) {
		Collection<OfferingMember> members = new ArrayList<OfferingMember>();
		for (OfferingMember member : this.findByOfferingId(offeringId, true)) {
			if (!Checker.isEmpty(member.getReceipt().getNumber())
					&& member.passed()) {
				members.add(member);
			}
		}
		return members;
	}

	@Deprecated
	public Collection<OfferingMember> findOldPassedByOfferingId(Long offeringId) {
		Collection<OfferingMember> members = new ArrayList<OfferingMember>();
		for (OfferingMember member : this.findByOfferingId(offeringId, true)) {
			if (!Checker.isEmpty(member.getReceipt().getNumber())
					&& member.getFinalTest().oldPassed()) {
				members.add(member);
			}
		}
		return members;
	}

	@Override
	public Collection<FinalTestResultReportItem> reportFinalTestResult(
			Long offeringId) {
		Collection<FinalTestResultReportItem> result = new ArrayList<FinalTestResultReportItem>();
		Collection<OfferingMember> members = this.findByOfferingId(offeringId,
				true);
		for (OfferingMember member : members) {
			result.add(new FinalTestResultReportItem(member.getReceipt()
					.getStudent().getFirstName(), member.getReceipt()
					.getMember().getTotalScore()));
		}
		return result;
	}

	@Override
	public Collection<OfferingMember> findByDate(Date fromDate, Date toDate) {
		Criteria criteria = this.createCriteria();
		criteria.createCriteria("receipt").createCriteria("offering").add(
				Restrictions.between("endDate", fromDate, toDate));
		return criteria.list();
	}

	/**
	 * CUONGPC
	 */
	
	@Override
	public List<OfferingMember> findByDateAndProgram(Date fromDate, Date toDate, Program program) {
		Criteria criteria = this.createCriteria();
		Criteria offeringCriteria = criteria.createCriteria("receipt").createCriteria("offering");
		offeringCriteria.add(Restrictions.between("endDate", fromDate, toDate));
		offeringCriteria.createCriteria("level").add(Restrictions.eq("program", program));
		return criteria.list();
	}

	@Override
	public Collection<OfferingMember> findByBirthDay(int month) {
		StringBuffer buffer = new StringBuffer();
		buffer.append("SELECT om FROM OfferingMember om ");
		buffer.append("WHERE om.receipt.offering.endDate >= :date ");
		buffer
				.append("AND month(om.receipt.student.dateOfBirth) = :monthOfBirth");
		Query query = this.getSession().createQuery(buffer.toString());

		query.setParameter("date", new Date());
		query.setParameter("monthOfBirth", month);

		return query.list();
	}
	
	@Override
	public Collection<OfferingMember> findByBirthDayAndProgram(int month, long programId) {
		StringBuffer buffer = new StringBuffer();
		buffer.append("SELECT om FROM OfferingMember om ");
		buffer.append("WHERE om.receipt.offering.endDate >= :date ");
		buffer
			.append("AND month(om.receipt.student.dateOfBirth) = :monthOfBirth ");
		if (programId != 0)
			buffer.append("AND om.receipt.offering.level.program.id = :programId");
		Query query = this.getSession().createQuery(buffer.toString());

		query.setParameter("date", new Date());
		query.setParameter("monthOfBirth", month);
		if (programId != 0)
			query.setParameter("programId", programId);

		return query.list();
	}

	@Override
	public Collection<OfferingMember> findByEndDate(Date startDateOfMonth, Date endDateOfMonth) {
		String sql = "SELECT mem.* FROM ncms_offering AS o, ncms_receipt AS re, ncms_offering_member AS mem, ncms_test AS test " +
			"	WHERE o.id = re.offering_id AND re.id = mem.offering_receipt_id AND test.offering_member_id = mem.id " +
			"		AND (o.end_date BETWEEN ? AND ?) AND test.test_type = 'final_test';";
		SQLQuery query = this.getSession().createSQLQuery(sql);
		query.setDate(0, startDateOfMonth).setDate(1, endDateOfMonth);
		return query.addEntity(OfferingMember.class).list();
	}

	@Override
	public Collection<OfferingMember> findAbsenceByEndDate(Long programId, Date startDateOfMonth, Date endDateOfMonth, boolean active) {
		String sql = "SELECT mem.* FROM ncms_offering AS o, ncms_receipt AS re, ncms_offering_member AS mem, ncms_test AS test, ncms_program AS pro, ncms_level AS LV " +
				"WHERE o.id = re.offering_id AND re.id = mem.offering_receipt_id AND test.offering_member_id = mem.id " +
				"AND o.level_id = LV.id AND LV.program_id = PRO.ID " +
				"AND (o.end_date BETWEEN ? AND ?) AND test.test_type = 'final_test' AND mem.active = ? " +
				"AND PRO.id = ? AND (test.listening ISNULL OR test.reading ISNULL OR test.speaking ISNULL OR test.writing ISNULL)";
		SQLQuery query = this.getSession().createSQLQuery(sql);
		query.setDate(0, startDateOfMonth).setDate(1, endDateOfMonth).setBoolean(2, active).setLong(3, programId);
		return query.addEntity(OfferingMember.class).list();
	}

	@Override	
	public Collection<OfferingMember> findAbsenceByMonthYear(int month, int year, boolean active) {
		Date startDate = DateUtil.getStartDateOfMonth(month, year);
		Date endDate = DateUtil.getEndDateOfMonth(month, year);
		String sql = "SELECT mem.* FROM ncms_offering AS o, ncms_receipt AS re, ncms_offering_member AS mem, ncms_test AS test, ncms_program AS pro, ncms_level AS LV " +
				"WHERE o.id = re.offering_id AND re.id = mem.offering_receipt_id AND test.offering_member_id = mem.id " +
				"AND o.level_id = LV.id AND LV.program_id = PRO.ID " +
				"AND (o.end_date BETWEEN ? AND ?) AND test.test_type = 'final_test' AND mem.active = ? " +
				"AND ((PRO.id in (9, 12, 13, 6) AND (test.speaking ISNULL OR test.writing ISNULL)) OR " +
				"(PRO.id in (17) AND (test.speaking ISNULL OR test.reading ISNULL OR test.listening ISNULL)))";
		SQLQuery query = this.getSession().createSQLQuery(sql);
		query.setDate(0, startDate).setDate(1, endDate).setBoolean(2, active);
		return query.addEntity(OfferingMember.class).list();
	}
}